package com.tz.farmer.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tz.farmer.model.BusinessInfo;
import com.tz.farmer.model.DetailsVo;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;


public interface BusinessInfoMapper extends BaseMapper<BusinessInfo> {
    int deleteByPrimaryKey(Integer id);

    int insert(BusinessInfo record);

    int insertSelective(BusinessInfo record);

    BusinessInfo selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(BusinessInfo record);

    int updateByPrimaryKey(BusinessInfo record);


    @Select("<script>" +
            "    SELECT\n" +
            "    id,\n" +
            "    business_person_name,\n" +
            "    business_name,\n" +
            "    business_address,\n" +
            "    update_time,\n" +
            "    unisc_id,\n" +
            "    scan_num,\n" +
            "    visit_count,\n" +
            "    phone,\n" +
            "    property,\n" +
            "    zip_code,\n" +
            "    scale,\n" +
            "    city,\n" +
            "    leading_product,\n" +
            "    is_delete\n" +
            "    FROM\n" +
            "    business_info"+
            " where property = #{property} and  is_delete = 0  " +
            " <if test = \"businessCode != null   \"  > and unisc_id = #{businessCode} </if> \n" +
            " <if test = \"keyword != null and keyword.trim() !=''  \" >and ( business_name like CONCAT('%', #{keyword},'%')  \n" +
            "    or unisc_id like CONCAT('%', #{keyword},'%') )</if> \n" +
            " <if test = \"city != null and city.trim() !=''  \" >and city = #{city} </if>  \n" +
            " <if test = \"nameorphone != null and nameorphone.trim() !=''  \" >and ( business_person_name like CONCAT('%', #{nameorphone},'%')  \n" +
            "    or phone like CONCAT('%', #{nameorphone},'%')  )</if> \n" +
            " <if test = \"businessAddress != null and businessAddress.trim() !=''  \" >and business_address like CONCAT('%', #{businessAddress},'%')  </if> \n" +
            " <if test=\"startTime != null and startTime.trim() != ''\">" +
            "   <![CDATA[   and ( STR_TO_DATE(update_time, '%Y-%m-%d %H:%i:%s') >=  STR_TO_DATE( '${startTime} 00:00:00', '%Y-%m-%d %H:%i:%s')   \n" +
            "     and STR_TO_DATE(update_time, '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE( '${endTime} 23:59:59', '%Y-%m-%d %H:%i:%s')  )  ]]> \n" +
            " </if> " +
            "  order by update_time desc " +
            "  limit #{n} , #{m} " +
            "</script>")
    List<BusinessInfo> getBusinessInfoList(@Param("businessCode") String businessCode,@Param("property")String property,@Param("keyword") String keyword, @Param("nameorphone")String nameorphone, @Param("businessAddress")String businessAddress,@Param("startTime") String startTime, @Param("endTime")String endTime,@Param("city")String city, @Param("n")int n, @Param("m")int m);

    @Select("<script>" +
            "    SELECT\n" +
            "  count(0) " +
            "    FROM\n" +
            "    business_info"+
            " where property = #{property} and  is_delete = 0  " +
            " <if test = \"businessCode != null   \"  > and unisc_id = #{businessCode} </if> \n" +
            " <if test = \"keyword != null and keyword.trim() !=''  \" >and ( business_name like CONCAT('%', #{keyword},'%')  \n" +
            "    or unisc_id like CONCAT('%', #{keyword},'%') )</if> \n" +
            " <if test = \"city != null and city.trim() !=''  \" >and city = #{city} </if>  \n" +
            " <if test = \"nameorphone != null and nameorphone.trim() !=''  \" >and ( business_person_name like CONCAT('%', #{nameorphone},'%')  \n" +
            "    or phone like CONCAT('%', #{nameorphone},'%')  )</if> \n" +
            " <if test = \"businessAddress != null and businessAddress.trim() !=''  \" >and business_address like CONCAT('%', #{businessAddress},'%')  </if> \n" +
            " <if test=\"startTime != null and startTime.trim() != ''\">" +
            "   <![CDATA[   and ( STR_TO_DATE(update_time, '%Y-%m-%d %H:%i:%s') >=  STR_TO_DATE( '${startTime} 00:00:00', '%Y-%m-%d %H:%i:%s')   \n" +
            "     and STR_TO_DATE(update_time, '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE( '${endTime} 23:59:59', '%Y-%m-%d %H:%i:%s')  )  ]]> \n" +
            " </if> " +
            "</script>")
    int countTotal(@Param("businessCode") String businessCode,@Param("property")String property,@Param("keyword") String keyword, @Param("nameorphone")String nameorphone, @Param("businessAddress")String businessAddress,@Param("startTime") String startTime, @Param("endTime")String endTime, @Param("city")String city );

    @Select("<script>SELECT\n" +
            "\tb.id AS id,\n" +
            "\tb.unisc_id AS uniscId,\n" +
            "\tb.business_name AS businessName,\n" +
            "\tb.scale AS scale,\n" +
            "\tb.leading_product AS leadingProduct,\n" +
            "\tp.weight AS weight,\n" +
            "\tp.p_name AS pName,\n" +
            "\tp.pick_time AS pickTime,\n" +
            "\ti.auth_type AS authType,\n" +
            "\ti.number AS number,\n" +
            "\ti.yield AS yield,\n" +
            "\ti.term_time AS termTime,\n" +
            "\td.url AS url \n" +
            "  FROM\n" +
            "\tbusiness_info b\n" +
            "\tLEFT JOIN product_info p ON b.id = p.lead_id\n" +
            "\tLEFT JOIN authentication_info i ON i.lead_id = b.id\n" +
            "\tLEFT JOIN operate_info o ON o.p_info_id = p.id\n" +
            "\tLEFT JOIN trade_info t ON t.p_info_id = p.id and  t.trade_time = DATE_FORMAT( FROM_UNIXTIME(#{tradeTime}/ 1000, '%Y-%m-%d %h:%i:%s' ), '%Y-%m-%d' ) \n" +
            "\tLEFT JOIN detection_info d ON d.p_info_id = p.id \n" +
            "  WHERE\n" +
            "\tb.property = 331002 \n" +
            "\tAND b.unisc_id = #{certificateCode} \n" +
            "\tAND p.p_name =#{goodsName} \n" +
            "\tAND i.product_name = #{goodsName} \n" +
            "GROUP BY\n" +
            "\tb.id</script>")
    List<DetailsVo> selectByNameList(@Param("goodsName") String goodsName,@Param("certificateCode") String certificateCode,@Param("tradeTime") String tradeTime);



    @Select("select b.id AS id,b.unisc_id AS uniscId,b.business_name AS businessName,b.scale AS scale,b.leading_product AS leadingProduct from business_info b where b.is_delete= 0 and b.id = #{leadId}")
    DetailsVo selectMainBodyInfo(@Param("leadId")  Integer leadId);

    @Select("SELECT id,weight,p_name,pick_time,lead_id FROM product_info  WHERE id = #{pid}  ")
    DetailsVo selectMainProductInfo(@Param("pid")Integer pid);

    @Select("SELECT i.id AS id,i.product_name AS pName,i.auth_type AS authType,i.number AS number,i.yield AS yield,i.term_time AS termTime FROM authentication_info i WHERE i.lead_id = #{lid}  AND i.product_name = #{goodsName}")
    DetailsVo selectMainAuthInfo(@Param("goodsName")String goodsName, @Param("lid")Integer lid);

    @Select("SELECT d.id AS id,d.url AS url FROM detection_info d WHERE d.p_info_id =#{id} ")
    List<DetailsVo> selectMainDetectInfo(@Param("id")Integer id);


    @Select("SELECT id FROM business_info  WHERE unisc_id =#{code} and is_delete=0")
    Map selectCountByCode(@Param("code")String code);

    @Insert("insert into znyp_record (znyp_Id) values (#{id})")
    int insertZnypRecord(@Param("id")String id);

    @Update("update znyp_record set page_count= (#{pageCount})")
    int updateZnypRecord(@Param("pageCount")String pageCount);

    @Select("SELECT count(1) FROM znyp_record  WHERE znyp_Id =#{id}")
    int selectZnypRecord(@Param("id")String id);

    @Select("SELECT count(1) FROM znyp_record")
    int selectZnypCount();
}







































